IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_GetUsersForManagement]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_GetUsersForManagement]
GO
create procedure [dbo].[cms_sp_GetUsersForManagement]
@SiteGroupId int,
@SiteId int,
@CurrentPage int,
@PageCapacity int
as
declare @resultCount int
declare @result table
(
UserId int not null,
SiteGroupId int null,
SiteId int null,
LoginName [nvarchar](150),
UserEmail [nvarchar](250),
UserName [nvarchar](250) null,
IsEnabled bit not null,
IsSystem bit not null,
CreatedBy int not null,
DateCreated datetime not null,
LastUpdatedBy int null,
DateLastUpdated datetime null,
UserCreatedBy nvarchar(250) null,
UserLastUpdatedBy nvarchar(250) null
)
insert into @result
(
UserId,
SiteId,
SiteGroupId,
LoginName,
UserEmail,
UserName,
IsEnabled,
IsSystem,
CreatedBy,
DateCreated,
LastUpdatedBy,
DateLastUpdated,
UserCreatedBy,
UserLastUpdatedBy
)
select 
	Users.UserId,
	Users.SiteId,
	Users.SiteGroupId,
	Users.LoginName,
	Users.UserEmail,
	Users.UserName,
	Users.IsEnabled,
	Users.IsSystem,
	Users.CreatedBy,
	Users.DateCreated,
	Users.LastUpdatedBy,
	Users.DateLastUpdated,
	Creators.UserName as UserCreatedBy,
	Updaters.UserName as UserLastUpdatedBy
from [dbo].[Users]
left join [dbo].[Users] as Creators
	on Creators.UserId = Users.CreatedBy
left join [dbo].[Users] as Updaters
	on Updaters.UserId = Users.LastUpdatedBy
where (Users.SiteGroupId is null and Users.SiteId is null) or
	(Users.SiteGroupId = @SiteGroupId and Users.SiteId is null) or
	(Users.SiteGroupId = @SiteGroupId and Users.SiteId = @SiteId)
select @resultCount = COUNT(UserId) from @result
if(@resultCount < @PageCapacity)
begin
	set @PageCapacity = @resultCount
end
select 
	@resultCount as TotalCount, 
	UserId,
	SiteId,
	SiteGroupId,
	LoginName,
	UserEmail,
	UserName,
	IsEnabled,
	IsSystem,
	CreatedBy,
	DateCreated,
	LastUpdatedBy,
	DateLastUpdated,
	UserCreatedBy,
	UserLastUpdatedBy
from
(
	select tbl.*, ROW_NUMBER() over (order by tbl.UserId) rownum
	from @result as tbl
) 
seq
where seq.rownum between @PageCapacity * (@CurrentPage - 1) + 1 and @PageCapacity * @CurrentPage


GO

